package com.htzl.excelcombine;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class FileMerge {

    public static void main(String[] args) {
        // D盘下merge文件夹路径
        String folderPath = "D:/merge";
        // 输出文件路径
        String outputFilePath = "D:/merge/汇总结果.xls";
        String basicEducationFilePath = "D:/merge/基础教育第一轮审验.xlsx"; // 基础教育文件路径

        // 读取基础教育文件并构建标题与解释说明的Map
        Map<String, String> explanationMap = readBasicEducationFile(basicEducationFilePath);

        File folder = new File(folderPath);
        File[] inputFiles = folder.listFiles((dir, name) ->
                !name.endsWith("审验.xlsx") && (name.endsWith(".xls") || name.endsWith(".xlsx"))
        );

        if (inputFiles == null || inputFiles.length == 0) {
            System.out.println("在D:/merge文件夹中没有找到任何Excel文件！");
            return;
        }

        try (Workbook outputWorkbook = new HSSFWorkbook()) { // 输出为.xls格式
            Sheet outputSheet = outputWorkbook.createSheet("汇总数据");

            // 设置表头（新增“解释说明”字段）
            String[] headers = {"县区代码", "市名称", "县区名称", "办学类型", "举办者", "学校代码", "学校名称", "问题", "解释说明"};
            Row headerRow = outputSheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // 行计数器
            int rowIndex = 1;

            // 遍历所有输入文件并合并数据
            for (File inputFile : inputFiles) {
                System.out.println("正在处理文件：" + inputFile.getName());

                try (FileInputStream fis = new FileInputStream(inputFile);
                     Workbook inputWorkbook = createWorkbook(fis, inputFile.getName())) {

                    Sheet inputSheet = inputWorkbook.getSheetAt(0);

                    // 获取A1单元格的内容并进行正则截取
                    String originalTitle = getCellValue(inputSheet.getRow(0).getCell(0)).trim();
                    String truncatedTitle = truncateBeforeTimestamp(originalTitle); // 正则截取标题
                    System.out.println("当前表格截取后的标题: " + truncatedTitle);

                    // 检查是否能在解释说明Map中找到匹配的解释说明
                    String explanation = explanationMap.getOrDefault(truncatedTitle, "无对应的解释说明");

                    // 从第二行开始读取（跳过表头）
                    for (int i = 2; i <= inputSheet.getLastRowNum(); i++) {
                        Row inputRow = inputSheet.getRow(i);
                        if (inputRow == null || isEmptyRow(inputRow)) continue; // 跳过空行

                        Row outputRow = outputSheet.createRow(rowIndex++);

                        // 读取相关列并写入新表格
                        boolean hasValue = false; // 标记是否有非空字段
                        for (int j = 0; j <= 6; j++) {
                            String cellValue = getCellValue(inputRow.getCell(j));
                            if (!cellValue.isEmpty()) {
                                hasValue = true;
                            }
                            outputRow.createCell(j).setCellValue(cellValue);
                        }

                        // 生成“问题”字段
                        StringBuilder problemBuilder = new StringBuilder(truncatedTitle);
                        for (int j = 7; j < inputRow.getLastCellNum(); j++) {
                            String columnName = getCellValue(inputSheet.getRow(1).getCell(j)); // 从第2行获取字段名
                            String cellValue = getCellValue(inputRow.getCell(j)); // 当前行的值
                            if (!cellValue.isEmpty() && !columnName.equals("数量")) {
                                problemBuilder.append(" ").append(columnName).append("-").append(cellValue);
                                hasValue = true; // 标记有值
                            }
                        }

                        // 如果所有字段都为空，跳过该行
                        if (!hasValue) {
                            rowIndex--;
                            continue;
                        }

                        // 写入“问题”字段
                        outputRow.createCell(7).setCellValue(problemBuilder.toString());

                        // 写入解释说明字段
                        outputRow.createCell(8).setCellValue(explanation);
                    }

                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            // 输出到新文件
            try (FileOutputStream fos = new FileOutputStream(outputFilePath)) {
                outputWorkbook.write(fos);
                System.out.println("合并完成，文件已保存到：" + outputFilePath);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 读取基础教育文件的A-D对应关系
    private static Map<String, String> readBasicEducationFile(String filePath) {
        Map<String, String> explanationMap = new HashMap<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = createWorkbook(fis, filePath)) {

            Sheet sheet = workbook.getSheetAt(0);
            // 从第2行开始读取（A2及以下）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String key = truncateBeforeTimestamp(getCellValue(row.getCell(0)).trim()); // 对标题进行正则截取
                    String value = getCellValue(row.getCell(3)).trim(); // D列（解释说明）
                    if (!key.isEmpty() && !value.isEmpty()) {
                        explanationMap.put(key, value);
                        System.out.println("加载解释说明: " + key + " -> " + value); // 调试信息
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return explanationMap;
    }

    // 判断一行是否为空行
    private static boolean isEmptyRow(Row row) {
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != CellType.BLANK && !getCellValue(cell).isEmpty()) {
                return false; // 只要有一个非空单元格，行就不是空行
            }
        }
        return true; // 所有单元格都为空，则是空行
    }

    // 创建 Workbook，支持 .xls 和 .xlsx
    private static Workbook createWorkbook(FileInputStream fis, String fileName) throws IOException {
        if (fileName.endsWith(".xls")) {
            return new HSSFWorkbook(fis);
        } else if (fileName.endsWith(".xlsx")) {
            return new XSSFWorkbook(fis);
        } else {
            throw new IllegalArgumentException("不支持的文件格式：" + fileName);
        }
    }

    // 获取单元格的值并转换为字符串
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        // 根据单元格类型获取相应的值
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    double value = cell.getNumericCellValue();
                    return (value == Math.floor(value)) ? String.valueOf((long) value) : String.valueOf(value);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue cellValue = evaluator.evaluate(cell);
                    switch (cellValue.getCellType()) {
                        case STRING:
                            return cellValue.getStringValue();
                        case NUMERIC:
                            double value = cellValue.getNumberValue();
                            return (value == Math.floor(value)) ? String.valueOf((long) value) : String.valueOf(value);
                        case BOOLEAN:
                            return String.valueOf(cellValue.getBooleanValue());
                        default:
                            return "";
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return "";
                }
            default:
                return "";
        }
    }

    // 截取到时间戳之前的部分
    private static String truncateBeforeTimestamp(String content) {
        Pattern pattern = Pattern.compile("\\d{1,2}-\\d{1,2}-\\d{1,2}\\(\\d{1,2}时\\d{1,2}分\\d{1,2}秒\\)");
        Matcher matcher = pattern.matcher(content);
        if (matcher.find()) {
            return content.substring(0, matcher.start()).trim();
        }
        return content; // 如果没有匹配到时间戳，则返回原内容
    }
}